﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace Team4Mock.DAO
{
    public class ServiceDAO
    {
        Helper h = new Helper();

        public DataTable GetData()
        {
            return h.GetData("Ser_Getdata");
        }
        public DataTable GetDataTrue()
        {
            return h.GetData("Ser_GetdataTrue");
        }
        public DataTable GetOrg()
        {
            return h.GetData("Ser_GetOrg");
        }
        public void DelServiceRef(int id, string pro)
        {
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            p[0].SqlDbType = SqlDbType.Int;
            h.ExecuteNonQuerry(pro, p);
        }
        public DataTable GetdataByID(int id)
        {
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            p[0].SqlDbType = SqlDbType.Int;
            return h.GetData("Ser_GetdataByID", p);
        }
        public DataTable GetRef(string pro)
        {
            return h.GetData(pro);
        }
        public DataTable GetProgram()
        {
            return h.GetData("Ser_GetProgram");
        }
        public bool CheckName(string name)
        {
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@name", name);
            p[0].SqlDbType = SqlDbType.NVarChar;
            return (h.GetValueString("Ser_CheckName", p) == "T");
        }
        public DataTable GetRefByID(int id, string pro)
        {
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@id", id);
            p[0].SqlDbType = SqlDbType.Int;
            return h.GetData(pro, p);
        }
        public void InsertRef(int idService, int Refid, string pro)
        {
            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("@SerID", idService);
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Refid", Refid);
            p[1].SqlDbType = SqlDbType.Int;
            h.ExecuteNonQuerry(pro, p);
        }
        public int GetID()
        {
            return h.GetValueInt("Ser_GetID");
        }
        public void InsertService(string name, string shortdes, string subtype, int contact, string clientdes, string serattend, string startexp, string startdate, string enddate, int serextend, int serextendyear, int serextendmonth, string desfull, string deptcode, string sertype, string serdesdel, string contractcode, string contractvalue, int stapayment, string refprocess, int timelimit, int timelimityear, int timelimitmonth, int programid, int active)
        {
            SqlParameter[] p = new SqlParameter[25];
            p[0] = new SqlParameter("@name", name);
            p[0].SqlDbType = SqlDbType.NVarChar;
            p[1] = new SqlParameter("@shortdes", shortdes);
            p[1].SqlDbType = SqlDbType.NVarChar;
            p[2] = new SqlParameter("@subtype", subtype);
            p[2].SqlDbType = SqlDbType.NVarChar;
            p[3] = new SqlParameter("@contact", contact);
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@clientdes", clientdes);
            p[4].SqlDbType = SqlDbType.NVarChar;
            p[5] = new SqlParameter("@serattend", serattend);
            p[5].SqlDbType = SqlDbType.NVarChar;
            p[6] = new SqlParameter("@active", active);
            p[6].SqlDbType = SqlDbType.Int;
            p[7] = new SqlParameter("@startexp", startexp);
            p[7].SqlDbType = SqlDbType.NVarChar;
            p[8] = new SqlParameter("@startdate", startdate);
            p[8].SqlDbType = SqlDbType.NVarChar;
            p[9] = new SqlParameter("@enddate", enddate);
            p[9].SqlDbType = SqlDbType.NVarChar;
            p[10] = new SqlParameter("@serextend", serextend);
            p[10].SqlDbType = SqlDbType.Int;
            p[11] = new SqlParameter("@serextenyear", serextendyear);
            p[11].SqlDbType = SqlDbType.Int;
            p[12] = new SqlParameter("@serextendmonth", serextendmonth);
            p[12].SqlDbType = SqlDbType.Int;
            p[13] = new SqlParameter("@desfull", desfull);
            p[13].SqlDbType = SqlDbType.NVarChar;
            p[14] = new SqlParameter("@deptcode", deptcode);
            p[14].SqlDbType = SqlDbType.NVarChar;
            p[15] = new SqlParameter("@sertype", sertype);
            p[15].SqlDbType = SqlDbType.NVarChar;
            p[16] = new SqlParameter("@serdesdel", serdesdel);
            p[16].SqlDbType = SqlDbType.NVarChar;
            p[17] = new SqlParameter("@contractcode", contractcode);
            p[17].SqlDbType = SqlDbType.NVarChar;
            p[18] = new SqlParameter("@contractvalue", contractvalue);
            p[18].SqlDbType = SqlDbType.NVarChar;
            p[19] = new SqlParameter("@stapayment", stapayment);
            p[19].SqlDbType = SqlDbType.Int;
            p[20] = new SqlParameter("@refprocess", refprocess);
            p[20].SqlDbType = SqlDbType.NVarChar;
            p[21] = new SqlParameter("@timelimit", timelimit);
            p[21].SqlDbType = SqlDbType.Int;
            p[22] = new SqlParameter("@timelimityear", timelimityear);
            p[22].SqlDbType = SqlDbType.Int;
            p[23] = new SqlParameter("@timelimitmonth", timelimitmonth);
            p[23].SqlDbType = SqlDbType.Int;
            p[24] = new SqlParameter("@programid", programid);
            p[24].SqlDbType = SqlDbType.Int;
            h.ExecuteNonQuerry("Ser_Insert", p);
        }
        public void UpdateService(string name, string shortdes, string subtype, int contact, string clientdes, string serattend, string startexp, string startdate, string enddate, int serextend, int serextendyear, int serextendmonth, string desfull, string deptcode, string sertype, string serdesdel, string contractcode, string contractvalue, int stapayment, string refprocess, int timelimit, int timelimityear, int timelimitmonth, int programid, int active, int serviceid)
        {
            SqlParameter[] p = new SqlParameter[26];
            p[0] = new SqlParameter("@name", name);
            p[0].SqlDbType = SqlDbType.NVarChar;
            p[1] = new SqlParameter("@shortdes", shortdes);
            p[1].SqlDbType = SqlDbType.NVarChar;
            p[2] = new SqlParameter("@subtype", subtype);
            p[2].SqlDbType = SqlDbType.NVarChar;
            p[3] = new SqlParameter("@contact", contact);
            p[3].SqlDbType = SqlDbType.Int;
            p[4] = new SqlParameter("@clientdes", clientdes);
            p[4].SqlDbType = SqlDbType.NVarChar;
            p[5] = new SqlParameter("@serattend", serattend);
            p[5].SqlDbType = SqlDbType.NVarChar;
            p[6] = new SqlParameter("@active", active);
            p[6].SqlDbType = SqlDbType.Int;
            p[7] = new SqlParameter("@startexp", startexp);
            p[7].SqlDbType = SqlDbType.NVarChar;
            p[8] = new SqlParameter("@startdate", startdate);
            p[8].SqlDbType = SqlDbType.NVarChar;
            p[9] = new SqlParameter("@enddate", enddate);
            p[9].SqlDbType = SqlDbType.NVarChar;
            p[10] = new SqlParameter("@serextend", serextend);
            p[10].SqlDbType = SqlDbType.Int;
            p[11] = new SqlParameter("@serextenyear", serextendyear);
            p[11].SqlDbType = SqlDbType.Int;
            p[12] = new SqlParameter("@serextendmonth", serextendmonth);
            p[12].SqlDbType = SqlDbType.Int;
            p[13] = new SqlParameter("@desfull", desfull);
            p[13].SqlDbType = SqlDbType.NVarChar;
            p[14] = new SqlParameter("@deptcode", deptcode);
            p[14].SqlDbType = SqlDbType.NVarChar;
            p[15] = new SqlParameter("@sertype", sertype);
            p[15].SqlDbType = SqlDbType.NVarChar;
            p[16] = new SqlParameter("@serdesdel", serdesdel);
            p[16].SqlDbType = SqlDbType.NVarChar;
            p[17] = new SqlParameter("@contractcode", contractcode);
            p[17].SqlDbType = SqlDbType.NVarChar;
            p[18] = new SqlParameter("@contractvalue", contractvalue);
            p[18].SqlDbType = SqlDbType.NVarChar;
            p[19] = new SqlParameter("@stapayment", stapayment);
            p[19].SqlDbType = SqlDbType.Int;
            p[20] = new SqlParameter("@refprocess", refprocess);
            p[20].SqlDbType = SqlDbType.NVarChar;
            p[21] = new SqlParameter("@timelimit", timelimit);
            p[21].SqlDbType = SqlDbType.Int;
            p[22] = new SqlParameter("@timelimityear", timelimityear);
            p[22].SqlDbType = SqlDbType.Int;
            p[23] = new SqlParameter("@timelimitmonth", timelimitmonth);
            p[23].SqlDbType = SqlDbType.Int;
            p[24] = new SqlParameter("@programid", programid);
            p[24].SqlDbType = SqlDbType.Int;
            p[25] = new SqlParameter("@serviceid", serviceid);
            p[25].SqlDbType = SqlDbType.Int;
            h.ExecuteNonQuerry("Ser_Update", p);
        }
        
        //public void InsertFunding(string source, int contact, string funamount, string funstart, string funend, string funtext, string funwhy, int donoranon, string donoramount, string funneed, int conneed, string conamount, string condetail, string daisingneed, string daisingrequi, int daisingcomple, string daisingcompledate, string daisingdondate, int daisingdonincre, int serviceid)
        //{
        //    SqlParameter[] p = new SqlParameter[20];
        //    p[0] = new SqlParameter("@source", source);
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@contact", contact);
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@funamount", funamount);
        //    p[2].SqlDbType = SqlDbType.NVarChar;
        //    p[3] = new SqlParameter("@funstart", funstart);
        //    p[3].SqlDbType = SqlDbType.SmallDateTime;
        //    p[4] = new SqlParameter("@funend", funend);
        //    p[4].SqlDbType = SqlDbType.SmallDateTime;
        //    p[5] = new SqlParameter("@funtext", funtext);
        //    p[5].SqlDbType = SqlDbType.NVarChar;
        //    p[6] = new SqlParameter("@funwhy", funwhy);
        //    p[6].SqlDbType = SqlDbType.NVarChar;
        //    p[7] = new SqlParameter("@donoranon", donoranon);
        //    p[7].SqlDbType = SqlDbType.Int;
        //    p[8] = new SqlParameter("@donoramount", donoramount);
        //    p[8].SqlDbType = SqlDbType.NVarChar;
        //    p[9] = new SqlParameter("@funneed", funneed);
        //    p[9].SqlDbType = SqlDbType.NVarChar;
        //    p[10] = new SqlParameter("@conneed", conneed);
        //    p[10].SqlDbType = SqlDbType.Int;
        //    p[11] = new SqlParameter("@conamount", conamount);
        //    p[11].SqlDbType = SqlDbType.NVarChar;
        //    p[12] = new SqlParameter("@condetail", condetail);
        //    p[12].SqlDbType = SqlDbType.NVarChar;
        //    p[13] = new SqlParameter("@daisingneed", daisingneed);
        //    p[13].SqlDbType = SqlDbType.NVarChar;
        //    p[14] = new SqlParameter("@daisingrequi", daisingrequi);
        //    p[14].SqlDbType = SqlDbType.NVarChar;
        //    p[15] = new SqlParameter("@daisingcomple", daisingcomple);
        //    p[15].SqlDbType = SqlDbType.Int;
        //    p[16] = new SqlParameter("@daisingcompledate", daisingcompledate);
        //    p[16].SqlDbType = SqlDbType.SmallDateTime;
        //    p[17] = new SqlParameter("@daisingdondate", daisingdondate);
        //    p[17].SqlDbType = SqlDbType.SmallDateTime;
        //    p[18] = new SqlParameter("@daisingdonincre", daisingdonincre);
        //    p[18].SqlDbType = SqlDbType.Int;
        //    p[19] = new SqlParameter("@serviceid", serviceid);
        //    p[19].SqlDbType = SqlDbType.Int;

        //    h.ExecuteNonQuerry("Ser_InsertFunding", p);
        //}
        //public void UpdateFunding(string source, int contact, string funamount, string funstart, string funend, string funtext, string funwhy, int donoranon, string donoramount, string funneed, int conneed, string conamount, string condetail, string daisingneed, string daisingrequi, int daisingcomple, string daisingcompledate, string daisingdondate, int daisingdonincre, int serviceid, int fundingid)
        //{
        //    SqlParameter[] p = new SqlParameter[21];
        //    p[0] = new SqlParameter("@source", source);
        //    p[0].SqlDbType = SqlDbType.NVarChar;
        //    p[1] = new SqlParameter("@contact", contact);
        //    p[1].SqlDbType = SqlDbType.Int;
        //    p[2] = new SqlParameter("@funamount", funamount);
        //    p[2].SqlDbType = SqlDbType.NVarChar;
        //    p[3] = new SqlParameter("@funstart", funstart);
        //    p[3].SqlDbType = SqlDbType.SmallDateTime;
        //    p[4] = new SqlParameter("@funend", funend);
        //    p[4].SqlDbType = SqlDbType.SmallDateTime;
        //    p[5] = new SqlParameter("@funtext", funtext);
        //    p[5].SqlDbType = SqlDbType.NVarChar;
        //    p[6] = new SqlParameter("@funwhy", funwhy);
        //    p[6].SqlDbType = SqlDbType.NVarChar;
        //    p[7] = new SqlParameter("@donoranon", donoranon);
        //    p[7].SqlDbType = SqlDbType.Int;
        //    p[8] = new SqlParameter("@donoramount", donoramount);
        //    p[8].SqlDbType = SqlDbType.NVarChar;
        //    p[9] = new SqlParameter("@funneed", funneed);
        //    p[9].SqlDbType = SqlDbType.NVarChar;
        //    p[10] = new SqlParameter("@conneed", conneed);
        //    p[10].SqlDbType = SqlDbType.Int;
        //    p[11] = new SqlParameter("@conamount", conamount);
        //    p[11].SqlDbType = SqlDbType.NVarChar;
        //    p[12] = new SqlParameter("@condetail", condetail);
        //    p[12].SqlDbType = SqlDbType.NVarChar;
        //    p[13] = new SqlParameter("@daisingneed", daisingneed);
        //    p[13].SqlDbType = SqlDbType.NVarChar;
        //    p[14] = new SqlParameter("@daisingrequi", daisingrequi);
        //    p[14].SqlDbType = SqlDbType.NVarChar;
        //    p[15] = new SqlParameter("@daisingcomple", daisingcomple);
        //    p[15].SqlDbType = SqlDbType.Int;
        //    p[16] = new SqlParameter("@daisingcompledate", daisingcompledate);
        //    p[16].SqlDbType = SqlDbType.SmallDateTime;
        //    p[17] = new SqlParameter("@daisingdondate", daisingdondate);
        //    p[17].SqlDbType = SqlDbType.SmallDateTime;
        //    p[18] = new SqlParameter("@daisingdonincre", daisingdonincre);
        //    p[18].SqlDbType = SqlDbType.Int;
        //    p[19] = new SqlParameter("@serviceid", serviceid);
        //    p[19].SqlDbType = SqlDbType.Int;
        //    p[20] = new SqlParameter("@fundingid", fundingid);
        //    p[20].SqlDbType = SqlDbType.Int;
        //    h.ExecuteNonQuerry("Ser_UpdateFunding", p);
        //}
    }
}